package lig.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import lig.com.dao.AgentInfoDAO;

import lig.com.conn.JdbcUtil;
import lig.com.dto.AgentInfoDTO;
import lig.com.dto.AgentInfoDTOList;


/**
 * *******************************
 * lig.com.dao
 * AgentInfoDAO.java
 * @Description :
 * *******************************
 * @author : Euijoo Lee
 * Feb 12, 2013 3:16:07 PM
 */
public class AgentInfoDAO {
	
	private static AgentInfoDAO instance = new AgentInfoDAO();
	
	/**
	 * ************************************
	 * 1. Summary :
	 * ************************************
	 * @Method Name : getInstnace
	 * @return
	 */
	public static AgentInfoDAO getInstnace() {
		return instance;
	}	
	
	/**
	 * ************************************
	 * 1. Summary : run query with agentInfo(conn, year1, year2)
	 * ************************************
	 * @Method Name : agentInfo
	 * @param conn
	 * @param year1
	 * @param year2
	 * @return
	 * @throws SQLException
	 */
	//public AgentInfoDTOList agentInfo(Connection conn, int year1, int year2) throws SQLException {
	public AgentInfoDTOList agentInfo(Connection conn) throws SQLException {
		
		StringBuffer sb = new StringBuffer();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {		
			/*
			sb.append("SELECT STATE, a.AGENT, AGENTNAME, Marketing, Underwriter, Ethnicity, Type, Status ");
			sb.append("FROM (SELECT DISTINCT AGENT FROM ODS_DWXP056_BK.DBO.DWXP056ALL WHERE LEFT(RIGHT(TABLE_GROUP,6),4) IN ("+year1+","+year2+"))a ");
			sb.append("inner join ");
			sb.append("( ");
			sb.append("select agent, state, AgentName, isnull(Marketing,'')Marketing, isnull(Underwriter,'')Underwriter, ");
			sb.append("		isnull(Ethnicity,'')Ethnicity, isnull(Type,'')Type, isnull(Status,'')Status ");
			sb.append("from Renewal.dbo.Allproducers ");
			sb.append(")b on a.agent = b.agent ");
			sb.append("ORDER BY STATUS, STATE,AGENTNAME ");
			*/
			
			sb.append("SELECT STATE, a.AGENT, AGENTNAME, Marketing, Underwriter, Ethnicity, Type, Status ");
			sb.append("FROM (SELECT DISTINCT AGENT FROM ODS_THIS.DBO.DWXP050)a ");
			sb.append("inner join ");
			sb.append("( ");
			sb.append("select agent, state, AgentName, isnull(Marketing,'')Marketing, isnull(Underwriter,'')Underwriter, ");
			sb.append("		isnull(Ethnicity,'')Ethnicity, isnull(Type,'')Type, isnull(Status,'')Status ");
			sb.append("from Renewal.dbo.Allproducers ");
			sb.append(")b on a.agent = b.agent ");
			sb.append("ORDER BY STATUS, STATE,AGENTNAME ");
			
			pstmt = conn.prepareStatement(sb.toString());
			
			//get query result
			rs = pstmt.executeQuery();
			
			// Set query information to agentInfoDtoList
			AgentInfoDTOList agentInfoDtoList = new AgentInfoDTOList();
			while(rs.next()){
				AgentInfoDTO agentInfoDto = new AgentInfoDTO();
				
				agentInfoDto.setState(rs.getString("STATE"));
				agentInfoDto.setAgent(rs.getString("AGENT"));
				agentInfoDto.setAgentName(rs.getString("AGENTNAME"));
				agentInfoDto.setMarketing(rs.getString("Marketing"));
				agentInfoDto.setUnderwriter(rs.getString("Underwriter"));
				agentInfoDto.setEthnicity(rs.getString("Ethnicity"));
				agentInfoDto.setType(rs.getString("Type"));
				agentInfoDto.setStatus(rs.getString("Status"));
				
				//get agent information into agentInfoDtoList
				agentInfoDtoList.getDtoList().add(agentInfoDto);		
			}
				
			return agentInfoDtoList;	
			
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
		}
	}
}
